package com.dingding.service.user.core.sql;

/**
 * 
 * @ClassName: UserLoginSQL 
 * @Description:用户登录表数据操作SQL
 * @author CHEHAUNBO 
 * @date 2016年2月19日 下午8:44:10
 * 
 */
public class UserLoginSQL {

	/**
	 * 
	 * 通过用户微信ID判断是否和手机号绑定
	 * @author CHEHUANBO3
	 * 
	 */
	public static final String IS_EXIST_USER_BY_WECHATID = "SELECT COUNT(*) FROM userlogin ul WHERE ul.weChatId = ?";

	/**
	 * 将微信ID和用户手机号绑定
	 * @author CHEHUANBO
	 * 
	 */
	public static final String BINDING_WECHATID_AND_TELLPHONE = "UPDATE userlogin UL SET UL.weChatId = ? WHERE UL.userName = ?";

	/**
	 * 用户参加优惠券赠送活动后，更改状态为已参加
	 * @author CHEHUANBO
	 */
	public static final String UPDATE_ISATTENDACtIVITY_STATUS = "UPDATE userlogin UL SET ul.isAttendActivity = 2 WHERE UL.uid = ?";

	/**
	 * 获取当前城市的所有用户的手机号
	 * @author CHEHUANBO
	 */
	public static final String GET_USERTELPHONE_BY_CITY = "SELECT UL.userName FROM userlogin UL INNER JOIN userdetail UD ON UD.loginId = UL.uid WHERE UD.currentCity LIKE ?";
	
	/**
	 * 获取车主信息及相关订单信息
	 * 订单完成后，乘客评价页面需要显示车主信息及部分订单信息，当前SQL由此产生
	 *
	 * 
	 * @author CHEHUANBO
	 * 
	 */
	public static final String GET_DRIVERINFO_AND_ORDERINFO = "SELECT uo.createTime,uo.totalMoney,ud.nickName,ud.userAvatar,vi.vehicleModel,vi.vehicleColor,vi.lisencePlate,ud.userLevel FROM userorder uo INNER JOIN userdetail ud ON (uo.grabOrderUserId = ud.loginId AND uo.orderType = 2) OR (uo.placeOrderUserId = ud.loginId AND uo.orderType = 1) INNER JOIN vehicleinfo vi ON vi.loginId = ud.loginId WHERE uo.oid = ?";

	/**
	 * 获取出租车车主信息及相关订单信息
	 * 订单完成后，乘客评价页面需要显示出租车车主信息及部分订单信息，当前SQL由此产生
	 *
	 * 
	 * @author YHQ
	 * 
	 */
	public static final String GET_TAXI_DRIVERINFO_AND_ORDERINFO = "SELECT uo.createTime,uo.totalMoney,ud.nickName,ud.userAvatar,vi.vehicleModel,vi.vehicleColor,vi.lisencePlate,ud.userLevel FROM userorder uo INNER JOIN specialuserinfo ud ON uo.grabOrderUserId = ud.userId INNER JOIN specialuservehicleinfo vi ON vi.userId = ud.userId WHERE uo.oid = ?";

	
	//查找用户详情表
	public static final String GET_USER_DETAIL_INFO = "SELECT  u.loginId,l.userRole,l.isDeposit,u.nickName,u.tellphone,u.age,u.sex,u.occupation,u.origin,u.userAvatar,u.homeAddress, u.homeLongitude,u.homeLatitude,u.companyAddress,u.companyLongitude,u.companyLatitude,u.startworkTime,u.endworkTime,v.lisencePlate,v.vstatus,v.vehicleType,v.vehicleModel,v.vehicleColor,v.xszImage,v.jszImage,v.vehicleImage,v.description,v.xszDescription,v.jszDescription,v.vehicleDescription,u.userTabloidAvatar,u.token,u.userLevel FROM userlogin l INNER JOIN userdetail u ON u.loginId = l.uid INNER JOIN vehicleinfo v ON v.loginId = l.uid WHERE l.uid = ?";

	/**
	 * 增加顺路推送次数
	 * @author YHQ
	 */
	public static final String INCREASE_PUSH_NUMBER = "UPDATE userlogin ul SET ul.pushNumber = (CASE WHEN ul.pushNumber > 0 THEN ul.pushNumber + 1 ELSE 1 END) WHERE ul.uid = ?";

	/**
	 * 减少一次用户优惠券数量
	 * @author YHQ
	 */
	public static final String REDUCE_COUPON_NUMBER = "UPDATE userlogin ul SET ul.couponNumber = (CASE WHEN ul.couponNumber > 0 THEN ul.couponNumber - 1 ELSE 0 END) WHERE ul.uid = ?";
	
	/**
	 * 获取专车的司机信息和订单信息
	 */
	public static final String GET_SPECIAL_DRIVERINFO_AND_ORDERINFO = "SELECT uo.createTime,uo.totalMoney,ud.nickName,ud.userAvatar,vi.vehicleModel,vi.vehicleColor,vi.lisencePlate,ud.userLevel FROM userspecialorder uo INNER JOIN specialuserinfo ud ON uo.grabOrderUserId = ud.userId INNER JOIN specialuservehicleinfo vi ON vi.userId = ud.userId WHERE uo.specialOrderId = ?";
}
